<%-- 
    Document   : DeleteAllObjects
    Created on : 20 Aug, 2011, 1:48:25 AM
    Author     : Vivekananda Moosani
--%>

<%@page contentType="text/html" pageEncoding="windows-1252"%>
<%@page import="java.util.Map.Entry"%>
<%@page import="java.util.HashMap"%>
<%@page import="java.util.Iterator"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.sql.DatabaseMetaData"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.ResultSetMetaData"%>
<%@page import="java.sql.Statement"%>
<%@page import="javax.sql.DataSource"%>
<%@page import="javax.naming.InitialContext"%>
<%@page import="javax.transaction.UserTransaction"%>
<%@page import="org.json.JSONWriter"%>
<%
//all that has to be done is to .. get DatabaseMetaData object
//remove tables, views, indexes, sequences ...
String datasourceName = (String)session.getAttribute("datasourceName");
InitialContext ic = new InitialContext();
DataSource ds = (DataSource)ic.lookup(datasourceName);
UserTransaction ut = (UserTransaction)ic.lookup("java:comp/UserTransaction");
ut.begin();
Connection conn = null;
try {
    conn = ds.getConnection();
    Statement st = conn.createStatement();
    DatabaseMetaData dbMd = conn.getMetaData();
    ResultSet rs = dbMd.getTables(null, null, null, null);
    System.out.println("Deleting db objects .. ");
    HashMap<String, String> toDelete = new HashMap<String, String>();
    while(rs.next()) {
        String objType = rs.getString("TABLE_TYPE");
        String objName = rs.getString("TABLE_NAME");
        String schema = rs.getString("TABLE_SCHEM");
        System.out.print(rs.getString("TABLE_CAT") + "::" + schema
                + "::" + objName + "::" + objType);
        //TODO: need to check if this is proper way across different types of databases
        //TODO: how is database catalog implemented in different databases
        String objFullName = (schema == null) ? (objName) : (schema + "." + objName);
        //obj type can be null
        if("TABLE".equals(objType)) {
            toDelete.put(objFullName, objType);
        } else if("VIEW".equals(objType)) {
            toDelete.put(objFullName, objType);
        } else if("SEQUENCE".equals(objType)) {
            toDelete.put(objFullName, objType);
        } else {
           System.out.println(" ..... skipped");
           continue;
        }
        System.out.println(" ... added to toDelete set of tables ");
    }

    int trials = 10;
    int attempt = 1;
    while(toDelete.size() != 0 && trials > 0) {
        System.out.println("Attempt :: " + attempt);
        attempt++;
        trials--;
        Iterator<Entry<String, String>> ite = toDelete.entrySet().iterator();
        while(ite.hasNext()) {
            Entry<String, String> entry = ite.next();
            String objFullName = entry.getKey();
            String objType = entry.getValue();
            String qString = null;
            
            if("TABLE".equals(objType)) {
                qString = "drop table " + objFullName;
            } else if("VIEW".equals(objType)) {
                qString = "drop view " + objFullName;
            } else if("SEQUENCE".equals(objType)) {
                qString = "drop sequence " + objFullName;
            } //else {
              // System.out.println("..... skipped");
              // continue;
            //}
            try {
                System.out.print(qString);
                int updateCount = st.executeUpdate(qString);
                System.out.println("..... " + updateCount);
                ite.remove();
            } catch(Exception ex) {
                System.out.println("..... " + ex.getMessage());
            }
       }
    }

    if(toDelete.size() > 0) {
        throw new Exception("Could not delete all tables .. so rolling back");
    }

    JSONWriter jw = new JSONWriter(out);
    jw.object();
    jw.key("allIsWell");
    jw.value(true);
    jw.endObject();
}
catch(Throwable th) {
    th.printStackTrace();
    if(ut != null) {
        try {ut.rollback();}catch(Exception ex){ex.printStackTrace();}
    }
    throw th;
}
finally {
    if(conn != null) {
        try {conn.close();}catch(Exception ex){ex.printStackTrace();}
    }
}

%>
